package UserDatabase;
/**\
 * @describe 连接用户数据库
 * 
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.cj.jdbc.result.ResultSetMetaData;


public class UserDatabase {
	Connection connect = null;
	Statement stem = null;
	ResultSet rs =null;
	
	String SQL = "";
	String columnName[];
	String record[][];
	int columnCount,recordAmount;
	
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/property_system","root","yang2532661314");
			//System.out.println(connect);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connect;
	}
	
	//返回密码与用户名进行登录匹配
	public boolean match(String uname,String upassword) {
		connect = getConnection();
		try {
			stem = connect.createStatement();//利用con对象得到statement，其可以向数据库发起sql语句并执行得到其内容
			rs = stem.executeQuery("select password from User where name = '"+uname+"' ");		
				
			if( rs != null) {
				while(rs.next()) {	
					String password = rs.getString("password");
					if(password.equals(upassword)) {
						return true;	
					}	
				}	
			}	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		return false;	
	}
	
	//判断用户是否是管理员
	public boolean matchtype(String uname,Object type) {
		connect = getConnection();
		try {
			stem = connect.createStatement();//利用con对象得到statement，其可以向数据库发起sql语句并执行得到其内容
			rs = stem.executeQuery("select type from User where name = '"+uname+"' ");
			
			if( rs != null) {
				while(rs.next()) {
					String type1 = rs.getString("type");
					if(type1.equals(type)) {
						return true;	
					}	
				}	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		return false;	
	}	
	
	//添加信息
	public void add(User u) {
		connect = getConnection();
		try {
			stem = connect.createStatement();
			int id = u.getId();
			String name = u.getName();
			String password = u.getPassword();
			String sex = u.getSex();
			Object type = u.getType();
			String age = u.getAge();
			String uno = u.getUno();
			String hno = u.getHno();
			String state = "已入户";
			int water = u.getWater();
			int warm = u.getWarm();
			int electricity = u.getElectricity();
			int gas = u.getGas();
			int property = u.getProperty();
			int house = u.getHouse();
			stem.executeUpdate("INSERT INTO User VALUES('"+id+"','"+name+"','"+type+"','"+sex+"','"+age+"','"+hno+"','"+uno+"','"+password+"' )");   
			stem.executeUpdate("INSERT INTO Bill VALUES("+id+","+water+","+electricity+","+gas+","+warm+","+property+","+house+" )");
			stem.executeUpdate("UPDATE House set state = '"+state+"' where hno = '"+hno+"'");
			stem.executeUpdate("INSERT INTO Card VALUES("+id+","+0+")");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		closegdbc();
	}
	
	
	//删除信息
	public void delete(String Username) {
		connect = getConnection();
		try {
			stem = connect.createStatement();
			stem.executeUpdate("update house set state = '未入户' where hno = (select hno from user where name = '"+Username+"')");
			stem.executeUpdate("delete from bill where id = (select id from user where name = '"+Username+"')");
			stem.executeUpdate("delete from card where id = (select id from user where name = '"+Username+"')");
			stem.executeUpdate("delete from user where name = '"+Username+"'");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		closegdbc();
	}
	
	//通用查询
	public void setSQL(String SQL) {
		this.SQL = SQL.trim();
	}
	
	public String[] getColumnName() {
		
		return columnName;
		
	}
	
	public String[][] getRecord(){
		
		startQuery();
		return record;
	}
	
	public void startQuery() {
		connect  = getConnection();
		try {
			stem = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs = stem.executeQuery(SQL);
			ResultSetMetaData metadata = (ResultSetMetaData) rs.getMetaData();
			columnCount = metadata.getColumnCount();
			columnName = new String[columnCount];
			for(int i=1;i<=columnCount;i++) {
				columnName[i-1] = metadata.getColumnName(i);
			}
			rs.last();
			recordAmount = rs.getRow();
			record = new String[recordAmount][columnCount];
			int i = 0;
			rs.beforeFirst();
			while(rs.next()) {
				for(int j=1;j<=columnCount;j++) {
					record[i][j-1] = rs.getString(j);
				}
				i++;
			}
			closegdbc();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	//修改信息
	public void updata(String SQL) {
		connect = getConnection();
		try {
			this.SQL = SQL.trim();
			stem = connect.createStatement();
			stem.executeUpdate(SQL);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		closegdbc();
	}
	
	
	//关闭数据库
		
	public void closegdbc() {	
		try {
			if(connect!=null) {
				connect.close();	
			}
			if(rs!=null) {
				rs.close();	
			}
			if(stem!=null) {
				stem.close();	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}	
	}
	
}
